﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SiGeSIB.Data.Providers;
using SiGeSIB.Data.Management;
using System.Data.SqlClient;
using System.Data;
using SiGeSIB.Data.Common;
using SiGeSIB.Data.Security;

namespace SiGeSIB.Data.Specialized.Providers
{
	public class ManagementDataProvider : BaseDataProvider, IManagementDataProvider
	{
		internal void _ReadContributors(SqlDataReader dr, Action<Contributor> action, bool readAddress)
		{
			while (dr.Read())
				action(_ReadContributor(dr, readAddress));
		}
		internal Contributor _ReadContributor(SqlDataReader dr, bool readAddress)
		{
			int IDX_Id = dr.GetOrdinal("Id");
			int IDX_Nome = dr.GetOrdinal("Nome");
			int IDX_IdEndereco = dr.GetOrdinal("IdEndereco");
			int IDX_TelefoneResidencial = dr.GetOrdinal("TelefoneResidencial");
			int IDX_TelefoneCelular = dr.GetOrdinal("TelefoneCelular");
			int IDX_Documento = dr.GetOrdinal("Documento");
			int IDX_IdTipoDocumento = dr.GetOrdinal("IdTipoDocumento");
			int IDX_IdTipo = dr.GetOrdinal("IdTipo");
			int IDX_IdEstadoCivil = dr.GetOrdinal("IdEstadoCivil");
			int IDX_DataNascimento = dr.GetOrdinal("DataNascimento");
			int IDX_DataMembresia = dr.GetOrdinal("DataMembresia");
			int IDX_TipoMembresia = dr.GetOrdinal("TipoMembresia");
			int IDX_DataAfastamento = dr.GetOrdinal("DataAfastamento");
			int IDX_MotivoAfastamento = dr.GetOrdinal("MotivoAfastamento");
			int IDX_CEP = -1;
			int IDX_IgrejaOrigem = dr.GetOrdinal("IgrejaOrigem");
			int IDX_Complemento = -1;

			if (readAddress)
			{
				IDX_CEP = dr.GetOrdinal("CEP");
				IDX_Complemento = dr.GetOrdinal("Complemento");
			}

			EContributorType type = dr.GetEnum<EContributorType>(IDX_IdTipo);
			Contributor cont = Contributor.Create(type);

			cont.Id = dr.GetInt32(IDX_Id);
			cont.Name = dr.GetString(IDX_Nome);
			cont.Phone = !dr.IsDBNull(IDX_TelefoneResidencial) ? dr.GetString(IDX_TelefoneResidencial) : null;
			cont.CellPhone = !dr.IsDBNull(IDX_TelefoneCelular) ? dr.GetString(IDX_TelefoneCelular) : null;
			cont.DocumentNumber = !dr.IsDBNull(IDX_Documento) ? dr.GetString(IDX_Documento) : null;
			cont.DocumentType = !dr.IsDBNull(IDX_IdTipoDocumento) ? dr.GetEnum<EDocumentType>(IDX_IdTipoDocumento) : (EDocumentType?)null;

			Address address = null;
			if (!dr.IsDBNull(IDX_IdEndereco) && readAddress)
			{
				address = _ReadAddress(dr);
				address.Id = dr.GetInt32(IDX_IdEndereco);
				address.Complement = dr.GetString(IDX_Complemento);
			}
			cont.Address = address;

			if (cont is Frequenter)
			{
				Frequenter frequenter = cont as Frequenter;
				frequenter.BirthdayDate = !dr.IsDBNull(IDX_DataNascimento) ? dr.GetDateTime(IDX_DataNascimento) : (DateTime?)null;
				frequenter.CivilState = !dr.IsDBNull(IDX_IdEstadoCivil) ? dr.GetEnum<ECivilState>(IDX_IdEstadoCivil) : ECivilState.Unknown;
			}

			if (cont is Member)
			{
				Member m = cont as Member;
				m.DisruptionDate = !dr.IsDBNull(IDX_DataAfastamento) ? dr.GetDateTime(IDX_DataAfastamento) : (DateTime?)null;
				m.DisruptionType = dr.GetEnum<EDisruptionType>(IDX_MotivoAfastamento);
				m.MembershipDate = dr.GetDateTime(IDX_DataMembresia);
				m.MembershipType = dr.GetEnum<EMembershipType>(IDX_TipoMembresia);
			}
			else if (type == EContributorType.Visitor)
			{
				Visitor v = cont as Visitor;
				v.OriginChurch = !dr.IsDBNull(IDX_IgrejaOrigem) ? dr.GetString(IDX_IgrejaOrigem) : null;
			}

			return cont;
		}

		internal void _ReadAddresses(SqlDataReader dr, Action<Address> action)
		{
			while (dr.Read())
			{
				action(_ReadAddress(dr));
			}
		}

		private Address _ReadAddress(SqlDataReader dr)
		{
			int IDX_CEP = dr.GetOrdinal("CEP");
			int IDX_Estado = dr.GetOrdinal("Estado");
			int IDX_Cidade = dr.GetOrdinal("Cidade");
			int IDX_Bairro = dr.GetOrdinal("Bairro");
			int IDX_Logradouro = dr.GetOrdinal("Logradouro");

			Address address = new Address();

			address.CEP = !dr.IsDBNull(IDX_CEP) ? dr.GetString(IDX_CEP) : string.Empty;
			address.State = !dr.IsDBNull(IDX_Estado) ? dr.GetString(IDX_Estado) : string.Empty;
			address.City = !dr.IsDBNull(IDX_Cidade) ? dr.GetString(IDX_Cidade) : string.Empty;
			address.District = !dr.IsDBNull(IDX_Bairro) ? dr.GetString(IDX_Bairro) : string.Empty;
			address.Street = !dr.IsDBNull(IDX_Logradouro) ? dr.GetString(IDX_Logradouro) : string.Empty;

			return address;
		}

		internal List<Contributor> _GetContributors(SqlConnection conn, int[] ids, EContributorType? type)
		{
			List<Contributor> contributors = null;

			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "GetContribuintes";

				if (ids != null && ids.Length > 0)
					cmd.AddParameterWithValues("Ids", ids.GetStringIds());

				if (type.HasValue)
					cmd.AddParameterWithValues("Tipo", (int)type.Value);

				cmd.AddParameterWithValues("Sep", SqlExtensions.CARRIAGE_RETURN);

				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					if (dr.HasRows)
						_ReadContributors(
							dr,
							c =>
							{
								if (contributors == null)
									contributors = new List<Contributor>();
								contributors.Add(c);
							},
							true);
				}
			}
			return contributors;
		}

		#region IManagementDataProvider Members
		public int InsertContributor(Contributor contributor)
		{
			int id = -1;

			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			{
				SqlTransaction tran = conn.BeginTransaction();

				try
				{
					#region Inserir endereco
					int adressId = -1;

					if (contributor.Address != null)
						adressId = _InsertAddress(conn, tran, contributor.Address);
					#endregion

					using (SqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.StoredProcedure;

						cmd.Transaction = tran;
						cmd.AddParameterWithValues("Nome", contributor.Name);
						cmd.AddParameterWithValues("IdTipo", (int)contributor.Type);

						if (adressId > 0)
							cmd.AddParameterWithValues("IdEndereco", adressId);
						if (contributor.Phone != null)
							cmd.AddParameterWithValues("TelefoneResidencial", contributor.Phone);
						if (contributor.CellPhone != null)
							cmd.AddParameterWithValues("TelefoneCelular", contributor.CellPhone);
						if (contributor.DocumentType != EDocumentType.Unknown)
						{
							cmd.AddParameterWithValues("IdTipoDocumento", (int)contributor.DocumentType);
							cmd.AddParameterWithValues("Documento", contributor.DocumentNumber);
						}

						if (contributor is Frequenter)
						{
							cmd.AddParameterWithValues("IdEstadoCivil", (int)(contributor as Frequenter).CivilState);
							cmd.AddParameterWithValues("DataNascimento", (contributor as Frequenter).BirthdayDate);
						}

						switch (contributor.Type)
						{
							case EContributorType.Away:
							case EContributorType.Member:
								Member m = contributor as Member;
								cmd.CommandText = "InserirMembro";
								cmd.AddParameterWithValues("DataMembresia", m.MembershipDate);
								cmd.AddParameterWithValues("TipoMembresia", (int)m.MembershipType);

								if (m.DisruptionDate.HasValue)
									cmd.AddParameterWithValues("DataAfastamento", m.DisruptionDate);

								cmd.AddParameterWithValues("MotivoAfastamento", (int)m.DisruptionType);
								break;
							case EContributorType.Visitor:
								cmd.CommandText = "InserirVisitante";
								cmd.AddParameterWithValues("IgrejaOrigem", ((Visitor)contributor).OriginChurch);
								break;
							case EContributorType.Contributor:
								cmd.CommandText = "InserirContribuinte";
								break;
							default:
								break;
						}

						id = cmd.ExecuteWithReturnValue();
					}
					tran.Commit();
				}
				catch (SqlException e)
				{
					tran.Rollback();
					throw e;
				}
			}

			return id;
		}
		public void UpdateContributor(Contributor contributor)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			{
				SqlTransaction tran = conn.BeginTransaction();
				try
				{
					#region Atualizar endereco
					if (contributor.Address != null)
						_UpdateAddress(conn, tran, contributor.Address);
					#endregion

					using (SqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.StoredProcedure;

						cmd.Transaction = tran;
						cmd.AddParameterWithValues("Id", contributor.Id);
						cmd.AddParameterWithValues("Nome", contributor.Name);
						cmd.AddParameterWithValues("IdTipo", (int)contributor.Type);

						if (contributor.Address != null && contributor.Address.Id > 0)
							cmd.AddParameterWithValues("IdEndereco", contributor.Address.Id);
						if (contributor.Phone != null)
							cmd.AddParameterWithValues("TelefoneResidencial", contributor.Phone);
						if (contributor.CellPhone != null)
							cmd.AddParameterWithValues("TelefoneCelular", contributor.CellPhone);
						if (contributor.DocumentType != EDocumentType.Unknown)
						{
							cmd.AddParameterWithValues("IdTipoDocumento", (int)contributor.DocumentType);
							cmd.AddParameterWithValues("Documento", contributor.DocumentNumber);
						}

						if (contributor is Frequenter)
						{
							cmd.AddParameterWithValues("IdEstadoCivil", (int)(contributor as Frequenter).CivilState);
							cmd.AddParameterWithValues("DataNascimento", (contributor as Frequenter).BirthdayDate);
						}

						switch (contributor.Type)
						{
							case EContributorType.Away:
							case EContributorType.Member:
								Member m = contributor as Member;
								cmd.CommandText = "AtualizarMembro";
								cmd.AddParameterWithValues("DataMembresia", m.MembershipDate);
								cmd.AddParameterWithValues("TipoMembresia", (int)m.MembershipType);
								
								if (m.DisruptionDate.HasValue)
									cmd.AddParameterWithValues("DataAfastamento", m.DisruptionDate);
								
								cmd.AddParameterWithValues("MotivoAfastamento", (int)m.DisruptionType);
								break;
							case EContributorType.Visitor:
								cmd.CommandText = "AtualizarVisitante";
								cmd.AddParameterWithValues("IgrejaOrigem", ((Visitor)contributor).OriginChurch);
								break;
							case EContributorType.Contributor:
								cmd.CommandText = "AtualizarContribuinte";
								break;
							default:
								break;
						}

						cmd.ExecuteNonQuery();
					}
					tran.Commit();
				}
				catch (SqlException e)
				{
					tran.Rollback();
					throw e;
				}
			}
		}
		public void RemoveContributor(int id)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			{
				List<Contributor> conts = _GetContributors(conn, new int[] { id }, null);

				Contributor cont = null;

				if (conts != null && conts.Count == 1)
				{
					cont = conts[0];

					using (SqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.StoredProcedure;
						cmd.CommandText = "RemoverContribuinte";
						cmd.AddParameterWithValues("Id", id);

						try
						{
							cmd.ExecuteNonQuery();
						}
						catch (SqlException e)
						{
							string descContributor = cont.Type.GetDescription().ToLower();
							if (e.Message.Contains("VINCULOMOVIMENTACAO"))
								throw new InvalidOperationException(string.Format("Não é possível remover o {0} selecionado, pois este possui vínculos com movimentações.", descContributor));
							else
								throw e;
						}
					}
				}
			}
		}

		public Member GetMember(int id)
		{
			List<Member> members = GetMembers(new int[] { id });

			return members != null && members.Count == 1 ? members[0] : null;
		}
		public List<Member> GetMembers()
		{
			return GetMembers(null);
		}
		public List<Member> GetMembers(int[] ids)
		{
			List<Contributor> contributors = GetContributors(ids);

			if (contributors != null)
				return contributors.ConvertAll<Member>(c => (Member)c);

			return null;
		}
		public Contributor GetContributor(int id)
		{
			List<Contributor> contributors = GetContributors(new int[] { id });

			return contributors != null && contributors.Count == 1 ? contributors[0] : null;
		}
		public List<Contributor> GetContributors(int[] ids)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
				return _GetContributors(conn, ids, null);
		}
		public List<Contributor> GetContributors(EContributorType type)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
				return _GetContributors(conn, null, type);
		}

		public Dictionary<string, Address> GetAddresses()
		{
			Dictionary<string, Address> addresses = null;

			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "GetEnderecos";

				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					if (dr.HasRows)
					{
						addresses = new Dictionary<string,Address>();

						_ReadAddresses(
							dr,
							address =>
							{
								addresses[address.CEP] = address;
							});
					}
				}
			}

			return addresses;
		}
		private int _InsertAddress(SqlConnection conn, SqlTransaction transaction, Address address)
		{
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "InserirEndereco";
				cmd.Transaction = transaction;

				cmd.AddParameterWithValues("CEP", address.CEP);
				cmd.AddParameterWithValues("Complemento", address.Complement);
				cmd.AddParameterWithValues("Logradouro", address.Street);
				cmd.AddParameterWithValues("Bairro", address.District);
				cmd.AddParameterWithValues("Cidade", address.City);
				cmd.AddParameterWithValues("Estado", address.State);

				return cmd.ExecuteWithReturnValue();
			}
		}
		private void _UpdateAddress(SqlConnection conn, SqlTransaction transaction, Address address)
		{
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "AtualizarEndereco";
				cmd.Transaction = transaction;

				cmd.AddParameterWithValues("Id", address.Id);
				cmd.AddParameterWithValues("CEP", address.CEP);
				cmd.AddParameterWithValues("Complemento", address.Complement);
				cmd.AddParameterWithValues("Logradouro", address.Street);
				cmd.AddParameterWithValues("Bairro", address.District);
				cmd.AddParameterWithValues("Cidade", address.City);
				cmd.AddParameterWithValues("Estado", address.State);

				cmd.ExecuteNonQuery();
			}
		}
		#endregion
	}
}
